-- @owner: haomeng802
-- @date: 2024/10
-- @testpoint: 验证普通行存表中数据使用union连接查询，部分测试点合理报错

@conn PrimaryDbBmode;

set dolphin.sql_mode = '';

--step1:创建普通行存表；expect:成功
drop table if exists t_table_union_0020;
create table t_table_union_0020(c15 xml,c16 enum('a'),c17 int,
c18 tinyint(1),c19 smallint,c20 mediumint(3),c21 integer,
c22 bigint(2),c23 int1,c24 int2,c25 int4,c26 int8,c28 money,
c29 bool,c30 boolean,c31 char,c32 nchar(2),c33 varchar(3),
c34 CHARACTER VARYING,c35 varchar2,c36 nvarchar(1),c37 text,
c38 blob,c39 raw,c40 bytea,c45 date,c46 time ,c47 time WITH TIME ZONE,
c48 time WITHOUT TIME ZONE,c49 timestamp,c50 timestamp WITH TIME ZONE,
c51 timestamp WITHOUT TIME ZONE,c52 SMALLDATETIME,c53 reltime,c54 abstime,
c55 point,c56 lseg,c57 box,c58 path,c59 path,c60 polygon,c61 circle,c62 cidr,
c63 inet,c64 macaddr,c65 bit,c66 bit varying(1),c67 tsvector,c68 tsquery,
c69 uuid,c70 jsonb,c71 hll,c72 int4range,c73 int8range,c74 numrange,c75 tsrange,
c76 tstzrange,c77 daterange,c78 oid,c79 cid,c80 xid,c81 tid,c82 REGCONFIG,
c83 REGDICTIONARY,c84 REGOPER,c85 REGOPERATOR,c86 REGPROC,c87 REGPROCEDURE,
c88 REGCLASS,c89 REGTYPE,c90 numeric,c91 decimal,c92 real,c93 double precision,
c94 hash16,c95 hash32,c96 tinyblob,c97 year,c98 varbinary,c99 int unsigned,
c100 smallint unsigned,c101 tinyint unsigned,c102 mediumblob,c103 fixed,c104 float,
c105 double,c106 double precision,c107 binary);

--step2:使用union连接查询两个select列的数量不一致；expect:合理报错
select c15,c16 from t_table_union_0020 union select c23 from t_table_union_0020;
select c15,c16 from t_table_union_0020 union all select c23 from t_table_union_0020;

--step3:使用union连接查询表中数据；expect:成功
select c17 from t_table_union_0020 union select c17 from t_table_union_0020;

--step4:创建视图；expect:成功
create or replace view t_view_union_0020 as select c17 from t_table_union_0020;

--step5:使用union连接select表和select视图；expect:成功
select c17 from t_table_union_0020 union select c17 from t_view_union_0020;
select c17 from t_table_union_0020 union all select c17 from t_view_union_0020;

--step6:创建自定义函数；expect:成功
drop function if exists t_function_union_0020;
CREATE OR REPLACE FUNCTION t_function_union_0020() RETURNS SETOF record
AS $$
    select 'a' union all select 'a';
$$LANGUAGE SQL;
/

--step7:使用union连接select表和select自定义函数；expect:成功
select c17 from t_table_union_0020 union select t_function_union_0020();
select c17 from t_table_union_0020 union all select t_function_union_0020();

--step8:使用union连接select视图和select自定义函数；expect:成功
select c17 from t_view_union_0020 union select t_function_union_0020();
select c17 from t_view_union_0020 union all select t_function_union_0020();

--step9:创建存储过程；expect:成功
drop procedure if exists t_procedure_union_0020;
create or replace procedure t_procedure_union_0020 AS insert_str varchar;
begin
    insert_str = "select 1 union select 'a';";
    execute immediate insert_str;
end;
/

--step10:使用union连接select存储过程和select自定义函数/视图/表；expect:成功
select t_procedure_union_0020() union select c17 from t_table_union_0020;
select t_procedure_union_0020() union all select c17 from t_table_union_0020;
select t_procedure_union_0020() union select c17 from t_view_union_0020;
select t_procedure_union_0020() union all select c17 from t_view_union_0020;
select t_procedure_union_0020() union select t_function_union_0020();
select t_procedure_union_0020() union all select t_function_union_0020();

--step11:清理环境；expect:成功
drop view if exists t_view_union_0020;
drop table if exists t_table_union_0020;
drop function if exists t_function_union_0020;
drop procedure if exists t_procedure_union_0020;
reset dolphin.sql_mode;
